#ods - dwd 每日装载语句

#!/bin/bash
#两个参数
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else
   echo "请传入日期参数"
   exit
fi

dwd_user_login_inc="
insert overwrite table ${APP}.dwd_user_login_inc partition(dt='$do_date')
select
    user_id,
    date_format(from_utc_timestamp(cast(ts as bigint),'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(cast(ts as bigint),'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    source_id,
    t4.area_code province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(
    select --
        user_id,
        source_id,
        area_code,
        version_code,
        mid_id,
        brand,
        model,
        operate_system,
        ts
    from
    (
        select
            user_id,
            source_id,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts,
            row_number() over (partition by session_id order by ts) rn
        from
        (
            select
                user_id,
                source_id,
                area_code,
                version_code,
                mid_id,
                brand,
                model,
                operate_system,
                ts,
                session_id
            from
            (
                select
                    common.uid user_id,
                    common.sc source_id,
                    common.ar area_code,
                    common.vc version_code,
                    common.mid mid_id,
                    common.ba brand,
                    common.md model,
                    common.os operate_system,
                    ts,
				    common.sid session_id
                from ${APP}.ods_log_inc
                where dt='$do_date'
                and page is not null
            )t1
        )t2
        where user_id is not null
    )t3
    where rn=1
)t4
left join
(
    select
        id province_id,
     area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on t4.area_code=bp.area_code;
"
dwd_user_register_inc="
insert overwrite table ${APP}.dwd_user_register_inc partition(dt='$do_date')
select
    ui.user_id,
    date_format(create_time,'yyyy-MM-dd') date_id,
    create_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
(

    select
        data.id user_id,
        data.create_time create_time
    from ${APP}.ods_user_info_inc
    where dt='$do_date'
    and type='insert'
    and data.create_time is not null
)ui
left join
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and common.uid is not null
)log
on ui.user_id=log.user_id
left join
(
     select
        id province_id
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.province_id;
"

dwd_comment_info_inc="
insert overwrite table ${APP}.dwd_comment_info_inc partition(dt='$do_date')
select
data.id,
data.user_id,
data.chapter_id,
data.course_id,
data.comment_txt,
data.create_time 
from ${APP}.ods_comment_info_inc
where dt='$do_date';
"
dwd_review_info_inc="
insert overwrite table ${APP}.dwd_review_info_inc partition(dt='$do_date')
select 
 data.id ,
 data.user_id,
 data.course_id,
 data.review_txt,
 cast(data.review_stars as decimal) review_stars ,
 data.create_time,
 subject_id,
 category_id 
 from ${APP}.ods_review_info_inc r 
 left join ${APP}.ods_course_info_full c  
 on r.data.course_id=c.id 
 left join ${APP}.ods_base_subject_info_full s
  on c.subject_id=s.id
where c.dt='$do_date';
"
dwd_trade_pay_detail_suc_inc="
with oi as (
    select data.id,
           data.user_id,
           data.province_id,
           data.origin_amount,
           data.coupon_reduce
    from ${APP}.ods_order_info_inc
    where dt = '$do_date'
      and (type = 'bootstrap-insert' or type = 'insert') 
),od as (
    select data.order_id,
           data.id,
           data.course_id,
           data.course_name
    from ${APP}.ods_order_detail_inc
    where dt = '$do_date'
    and (type = 'bootstrap-insert' or type = 'insert') 
),py as (
    select data.order_id,
           data.out_trade_no,
           data.alipay_trade_no,
           data.payment_type,
           data.callback_time,
            date_format(data.callback_time, 'yyyy-MM-dd') date_id,
           data.total_amount
    from ${APP}.ods_payment_info_inc
    	where dt = '$do_date'
        and (type = 'bootstrap-insert' or type = 'insert') and data.callback_time is not null)
insert overwrite table ${APP}.dwd_trade_pay_detail_suc_inc partition (dt='$do_date')
select
    od.id,
    py.order_id,
    user_id,
    course_id,
    course_name,
    province_id,
    alipay_trade_no,
    out_trade_no,
    payment_type,
    date_format(callback_time, 'yyyy-MM-dd'),
    callback_time,
    origin_amount,
    coupon_reduce,
    total_amount
from py left join od
on py.order_id = od.order_id
left join oi
on od.order_id = oi.id;
"
dwd_traffic_page_view_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_traffic_page_view_inc partition (dt='$do_date')
select
    province_id,
    brand,
    channel,
    is_new,
    model,
    mid_id,
    operate_system,
    user_id,
    version_code,
    page_item,
    page_item_type,
    last_page_id,
    page_id,
    source_type,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') view_time,
    concat(mid_id,'-',last_value(session_start_point,true) over (partition by mid_id order by ts)) session_id,
    during_time
from
(
    select
        common.ar area_code,
        common.ba brand,
        common.ch channel,
        common.is_new is_new,
        common.md model,
        common.mid mid_id,
        common.os operate_system,
        common.uid user_id,
        common.vc version_code,
        common.sc source_type
        page.during_time,
        page.item page_item,
        page.item_type page_item_type,
        page.last_page_id,
        page.page_id,
        ts,
        if(page.last_page_id is null,ts,null) session_start_point
    from ${APP}.ods_log_inc
    where dt='$do_date'
    and page is not null
)log
left join
(
    select
        id province_id,
        area_code
    from ${APP}.ods_base_province_full
    where dt='$do_date'
)bp
on log.area_code=bp.area_code;
"
dwd_trade_order_detail_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_trade_order_detail_inc partition(dt)
select
    od.id,
    od.order_id,
    oi.user_id,
    od.course_id,
    od.course_name,
    od.create_time,
    od.update_time,
    oi.province_id,
    date_format(od.create_time, 'yyyy-MM-dd') date_id,
    od.origin_amount,
    od.coupon_reduce,
    od.final_amount,
    date_format(od.create_time,'yyyy-MM-dd')
from
(
    select
        data.id,
        data.course_id,
        data.course_name,
        data.order_id,
        data.origin_amount,
        data.coupon_reduce,
        data.final_amount,
        data.create_time as create_time,
        data.update_time
    from ${APP}.ods_order_detail_inc
    where dt = '$do_date'
    and (type = 'bootstrap-insert' or type = 'insert') 
) od
left join
(
    select
        data.id,
        data.user_id,
        data.province_id
    from ${APP}.ods_order_info_inc
    where dt = '$do_date'
    and (type = 'bootstrap-insert' or type = 'insert') 
) oi
on od.order_id = oi.id;
"
dwd_video_audition_play_inc="
set hive.cbo.enable=false;
insert overwrite table ${APP}.dwd_video_audition_play_inc partition (dt='$do_date')
select
       t2.sid,
       t2.uid,
       t2.video_id,
       t3.id chapter_id,
       t2.is_first,
       t2.view_time,
       t2.date_id,
       t3.course_id,
       t3.course_name,
       t3.subject_id,
       t3.subject_name,
       t3.category_id,
       t3.category_name
from (
         select sid,
                uid,
                video_id,
                if(play_sec = position_sec, 1, 0) is_first,
                view_time,
                date_id
         from (
                  select uid,
                         sid,
                         video_id,
                         play_sec,
                         position_sec,
                         view_time,
                         date_id,
                         row_number() over (partition by uid,sid,video_id order by position_sec) rt
                  from ${APP}.dwd_traffic_appvideo_play_inc
                  where dt = '$do_date'
              ) t1
         where rt = 1
     ) t2
left join (
    select
           video_id,
           id,
           course_id,
           course_name,
           subject_id,
           subject_name,
           category_id,
           category_name
    from ${APP}.dim_chapter_full
    where dt='$do_date'
) t3 on t2.video_id=t3.video_id;
"
dwd_trade_cart_add_inc="
insert overwrite table ${APP}.dwd_trade_cart_add_inc partition(dt='$do_date')
select
    id,
    user_id,
    course_id,
    date_id,
    create_time
from
(
    select
        data.id,
        data.user_id,
        data.course_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd') date_id,
        date_format(from_utc_timestamp(ts*1000,'GMT+8'),'yyyy-MM-dd HH:mm:ss') create_time
    from ${APP}.ods_cart_info_inc
    where dt='$do_date'
    and type='insert'
)ci
"
dwd_interaction_questiontest_inc="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dwd_interaction_questiontest_inc partition (dt='$do_date')
SELECT
	t1.id,
	t1.user_id,
	t1.paper_id,
	paper_title ,
    course_id,
	course_name,
	question_id,
	date_format(t1.create_time, 'yyyy-MM-dd') date_id,
	id_correct,
	score,
	duration_sec,
   date_format(t1.create_time, 'yyyy-MM-dd')
	FROM (
	SELECT
		data.id ,
		data.user_id,
		data.paper_id,
		data.question_id,
		data.id_correct,
		data.create_time
		FROM ${APP}.ods_test_exam_question_inc
		WHERE dt=''$do_date''
	    and TYPE='insert'
	    ) t1
		join (
		 SELECT
			data.paper_id,
			data.duration_sec,
			data.score
		      FROM ${APP}.ods_test_exam_inc
			  WHERE dt=''$do_date''
	          and TYPE='insert')t2
		    ON t1.paper_id=t2.paper_id
		join (
		 SELECT
			id,
			paper_title,
			course_id
		       FROM ${APP}.ods_test_paper_full
			   WHERE dt='$do_date') t3
		    ON t3.id=t1.paper_id
		join(
			SELECT
				id,
				course_name
		        FROM ${APP}.ods_course_info_full
				WHERE dt='$do_date') t4
		    ON t4.id=t3.course_id;
"
dwd_study_user_chapter_process_inc="
insert overwrite table ${APP}.dwd_study_user_chapter_process_inc partition (dt='$do_date')
select
data.id,
data.course_id,
data.chapter_id,
data.user_id,
data.position_sec,
data.create_time,
data.update_time,
data.deleted
from
${APP}.ods_user_chapter_process_inc
where dt='$do_date';
"
case $1 in
    "dwd_user_login_inc" )
        hive -e "$dwd_user_login_inc"
    ;;
    "dwd_user_register_inc" )
        hive -e "$dwd_user_register_inc"   
    ;;
    "dwd_comment_info_inc" )
        hive -e "$dwd_comment_info_inc"
    ;;   
    "dwd_review_info_inc" )
        hive -e "$dwd_review_info_inc"
    ;;
    "dwd_trade_pay_detail_suc_inc" )
    hive -e "$dwd_trade_pay_detail_suc_inc"
    ;;   
    "dwd_traffic_page_view_inc" )
        hive -e "$dwd_traffic_page_view_inc"
    ;;
    "dwd_trade_order_detail_inc" )
    hive -e "$dwd_trade_order_detail_inc"
    ;;
    "dwd_video_audition_play_inc" )
    hive -e "$dwd_video_audition_play_inc"
    ;;
    "dwd_trade_cart_add_inc")
    hive -e "$dwd_trade_cart_add_inc"
    ;;
    "dwd_interaction_questiontest_inc")
    hive -e "dwd_interaction_questiontest_inc" 
    ;;
    "dwd_study_user_chapter_process_inc")
    hive -e "dwd_study_user_chapter_process_inc"
    ;;  
    "all" )
        hive -e "$dwd_user_login_inc$dwd_user_register_inc$dwd_comment_info_inc$dwd_review_info_inc$dwd_trade_pay_detail_suc_inc$dwd_traffic_page_view_inc$dwd_trade_order_detail_inc$dwd_video_audition_play_inc$dwd_trade_cart_add_inc$dwd_interaction_questiontest_inc$dwd_study_user_chapter_process_inc"
    ;;
esac





